package com.smartwebx.fr.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Repository;

import com.smartwebx.fr.util.StationFinalFields;


@Repository
public class ExecDao {

	/**
	 * 根据参数获得结果集
	 * 
	 * @param callSql
	 *            需要执行的 sql或 存储过程
	 * @param paramList
	 *            所需的参数，如果没有参数可以为 null
	 * @param jdbcTemplate
	 *            请求所属的数据库名（在spring环境中配置）
	 * @param haveHeader
	 *            是否包含表头
	 * @return
	 */
	public List<List<String>> getResultByParam(String callSql,
			final List<String> paramList, JdbcTemplate jdbcTemplate,final Boolean hasHeader) {

		List<List<String>> tableData = null;

		tableData = jdbcTemplate.execute(callSql,
				new PreparedStatementCallback<List<List<String>>>() {

					public List<List<String>> doInPreparedStatement(
							PreparedStatement ps) throws SQLException,
							DataAccessException {

						if (paramList != null) {
							for (int i = 0; i < paramList.size(); i++) {
								String param = paramList.get(i);

								if (param.equalsIgnoreCase("null")) {
									ps.setNull(i + 1, java.sql.Types.VARCHAR);
								} else {
									ps.setString(i + 1, paramList.get(i));
								}
							}
						}
						

						ResultSet rs = ps.executeQuery();

						ResultSetMetaData rsmd = rs.getMetaData();
						// 获得总列数
						int columnCount = rsmd.getColumnCount();

						List<List<String>> moreLineList = new ArrayList<List<String>>();

						boolean firstIn = true;
						while (rs.next()) {
							List<String> lineList = new ArrayList<String>();

							if (firstIn && hasHeader) {// 加入列名
								firstIn = false;

								for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
									// 获得列名
									lineList.add(rsmd
											.getColumnName(columnIndex).trim());

								}

								moreLineList.add(lineList);
								lineList = new ArrayList<String>();
							}

							for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {

								String pointData = rs.getString(columnIndex);
								if (pointData != null) {
									lineList.add(pointData.trim());
								} else {
									// 如果结果集为空，则使用指定符号代替
									lineList
											.add(StationFinalFields.No_Data_Point);
								}

							}
							moreLineList.add(lineList);
						}

						return moreLineList;
					}
				});

		return tableData;
	}
}
